Schema | ${Schema_Default} |
Table Name | outbound_dest_organisation |
S3 URL Location | ${ONEMD_S3_Target_Folder} |
S3 Object Prefix | ETH MDM21 Organization.txt |
IAM Role Arn | arn:aws:iam::<aws-account-id>:role/<role-name> |
Generate Manifest | No |
Data File Type | Delimited |
Delimiter | | |
Compress Data | No |
Null As | |
Escape | No |
Allow Overwrites | Yes |
Parallel | No |
Add Quotes | No |
Max File Size (MB) | |
Include Header | No |
Encryption | None |
Source Type | S3 |
Source URL | s3://ethicon/outbound/One MD Testing /Output files/ETH MDM21 Organization.txt000 |
Unpack ZIP file | No |
Target Type | SFTP |
Gzip data | No |
Target Object Name | ETH MDM21 Organization.txt |
Set Home Directory as Root | No |
Target URL | https://smft.axtria.com/OneMD |
Target Username | JnJ_Ethicon |
Target Password | ******** |
Target SFTP Key |
Main Table | Get Start Date |
Main Table Alias | start |
Joins | outbound_dest_fiscal_calendar, fiscal, Left |
Join Expressions | "start"."filter_efftv_end_dt"="fiscal"."efftv_end_dt", start_Left_fiscal |
Output Columns | start.algn_struc_cd, algn_struc_cd, start.geo_id, geo_id, start.geo_nm, geo_nm, start.level_cd, level_cd, start.filter_efftv_start_dt, filter_efftv_start_dt, start.filter_efftv_end_dt, filter_efftv_end_dt, start.last_updated, last_updated, start.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt, fiscal.fiscal_year_wk_end_dt, fiscal_year_wk_end_dt |
Include Input Columns | Yes |
Calculations | CASE WHEN "fiscal_year_wk_start_dt" is NULL THEN '1900-01-01' ELSE "fiscal_year_wk_start_dt" END, EFFTV_START_DT_new, CASE WHEN "fiscal_year_wk_end_dt" is NULL THEN '9999-12-31' ELSE "fiscal_year_wk_end_dt" END, EFFTV_END_DT_new, DATE_PART_YEAR(to_date("last_updated",'YYYY-mm-dd')) || lpad(extract(month from(to_date("last_updated",'YYYY-mm-dd'))),2,'00') || lpad(extract(day from(to_date("last_updated",'YYYY-mm-dd'))),2,'00'), LAST_UPDATED_DT_new, len("geo_id"), geo_id_length |
Main Table | Geo Id =2 digit |
Main Table Alias | c1 |
Joins | level CD is division, c2, Left |
Join Expressions | "c1"."algn_struc_cd"="c2"."upper_algn_struc_cd" and "c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new" and "c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new", c1_Left_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.geo_nm, geo_nm, c1.level_cd, level_cd, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c1.last_updated_dt_new, last_updated_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt |
Include Input Columns | Yes |
Calculations | case when "efftv_start_dt">"new_efftv_start_dt" then "efftv_start_dt" else "new_efftv_start_dt" end, EFFTV_START_DT_2, case when "efftv_end_dt">"new_efftv_end_dt" then "new_efftv_end_dt" else "efftv_end_dt" end, EFFTV_END_DT_2, case when "level_cd"='Nation' then "lower_algn_struc_cd" else "geo_id" end, GEO_ID_2 |
Main Table | Update Upper Terr & Constants |
Main Table Alias | c1 |
Joins | flag(included recs), c2, Inner |
Join Expressions | "c1"."algn_struc_cd" = "c2"."algn_struc_cd" and "c1"."upper_geo_id" = "c2"."geo_id", c1_Inner_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.org id prefix, org id prefix, c1.franchise code, franchise code, c1.sales org division code, sales org division code, c1.sales org name, sales org name, c1.upper_geo_id, upper_geo_id, c1.provider id, provider id, c1.last_updated_dt, last_updated_dt, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c2.level_cd, upper_level_cd |
Include Input Columns | Yes |
Calculations | "team_id" , geo_id_new, left("upper_level_cd",1), PARENT ORGANIZATION ID, trim("geo_nm") , geo_nm_new, case when left("level_cd",1) = 'S' then 'T' else left("level_cd",1) end , ORGANIZATION ID, case when DATE_PART_YEAR(to_date("efftv_end_dt",'YYYY-mm-dd')) ='9999' then NULL else "efftv_end_dt" end, end_date_new |
Column Mapping | algn_struc_cd, algn_struc_cd, team_id, team_id, level_cd, level_cd, org id prefix, org id prefix, franchise code, franchise code, sales org division code, sales org division code, sales org name, sales org name, upper_geo_id, upper_geo_id, provider id, provider id, last_updated_dt, last_updated_dt, efftv_start_dt, efftv_start_dt, end_date_new, efftv_end_dt, upper_level_cd, upper_level_cd, geo_id_new, geo_id, parent organization id, parent organization id, geo_nm_new, geo_nm, organization id, organization id |
Main Table | Aggregate Before Export |
Main Table Alias | c1 |
Joins | Param: Alignment Structure, c2, Left |
Join Expressions | "c1"."algn_struc_cd"="c2"."algn_struc_cd" , c1_Left_c2 |
Output Columns | c1.provider id, provider id, c1.geo_id, geo_id, c1.organization id, organization id, c1.parent organization id, parent organization id, c1.upper_geo_id, upper_geo_id, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.last_updated_dt, last_updated_dt, c1.algn_struc_cd, algn_struc_cd, c1.max_geo_nm, geo_nm, c1.max_sales org division code, sales org division code, c1.max_sales org name, sales org name, c1.max_franchise code, franchise code, c2.algn_struc_nm, algn_struc_nm |
Main Table | Get Alignment Str Name |
Main Table Alias | c1 |
Joins | outbound_src_geo_attributes, c2, Left |
Join Expressions | "c1"."algn_struc_cd"="c2"."algn_struc_cd" and "c1"."geo_id"="c2"."geo_id" and ("c1"."efftv_start_dt" between "c2"."efftv_start_dt" and "c2"."efftv_end_dt"), c1_Left_c2 |
Output Columns | c1.provider id, provider id, c1.geo_id, geo_id, c1.organization id, organization id, c1.parent organization id, parent organization id, c1.upper_geo_id, upper_geo_id, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.last_updated_dt, last_updated_dt, c1.algn_struc_cd, algn_struc_cd, c1.geo_nm, geo_nm, c1.sales org division code, sales org division code, c1.sales org name, sales org name, c1.franchise code, franchise code, c1.algn_struc_nm, algn_struc_nm, c2.attribute_cat, attribute_cat, c2.attribute_type, attribute_type |
Include Input Columns | Yes |
Calculations | case when "efftv_start_dt" is NULL then '' else DATE_PART_YEAR(to_date("efftv_start_dt",'YYYY-mm-dd')) || lpad(extract(month from(to_date("efftv_start_dt",'YYYY-mm-dd'))),2,'00') || lpad(extract(day from(to_date("efftv_start_dt",'YYYY-mm-dd'))),2,'00') end , start date new, case when "efftv_end_dt" is NULL then '' else DATE_PART_YEAR(to_date("efftv_end_dt",'YYYY-mm-dd')) || lpad(extract(month from(to_date("efftv_end_dt",'YYYY-mm-dd'))),2,'00') || lpad(extract(day from(to_date("efftv_end_dt",'YYYY-mm-dd'))),2,'00') end , end date new, case when "last_updated_dt" is NULL then '' else DATE_PART_YEAR(to_date("last_updated_dt",'YYYY-mm-dd')) || lpad(extract(month from(to_date("last_updated_dt",'YYYY-mm-dd'))),2,'00') || lpad(extract(day from(to_date("last_updated_dt",'YYYY-mm-dd'))),2,'00') end , last updated new, Left("sales org name",30), sales org name |
Schema | ${Schema_Default} |
Target Table Name | outbound_dest_organisation |
Fix Data Type Mismatches | Yes |
Column Mapping | provider id, provider id, geo_id, organization id, organization id, organization type code, geo_nm, organization name, sales org division code, sales org division code, sales org name, sales org team code, franchise code, franchise code, upper_geo_id, parent organization id, parent organization id, parent organization type code, start date new, organization effective date, end date new, organization end date, last updated new, last update date, algn_struc_cd, account alignment structure code, algn_struc_nm, account alignment structure name, attribute_cat, territory position category, attribute_type, territory position type |
Truncate | Truncate |
Automatic Compression | No |
Main Table | Update Final Constants(output) |
Main Table Alias | c1 |
Joins | Agg for Oldest, c2, Inner |
Join Expressions | "c1"."geo_id"="c2"."geo_id" and "c1"."parent organization id"="c2"."parent organization id" and "c1"."upper_geo_id"="c2"."upper_geo_id" and "c1"."organization id"="c2"."organization id" and "c1"."efftv_start_dt"="c2"."min_efftv_start_dt", c1_Inner_c2 |
Output Columns | c1.algn_struc_cd, c1_algn_struc_cd, c1.geo_id, c1_geo_id, c1.org id prefix, org id prefix, c1.franchise code, franchise code, c1.sales org division code, sales org division code, c1.sales org name, sales org name, c1.upper_geo_id, c1_upper_geo_id, c1.provider id, provider id, c1.last_updated_dt, last_updated_dt, c1.efftv_start_dt, efftv_start_dt, c1.upper_level_cd, upper_level_cd, c1.parent organization id, c1_parent organization id, c1.geo_nm, geo_nm, c1.organization id, c1_organization id, c1.efftv_end_dt, efftv_end_dt |
Main Table | Get Upper Territory Level |
Main Table Alias | gutl |
Joins | Filter Active Geographies, fag, Left |
Join Expressions | "gutl"."geo_id" = "fag"."geo_id", gutl_Left_fag |
Output Columns | gutl.algn_struc_cd, algn_struc_cd, gutl.geo_id, geo_id, gutl.team_id, team_id, gutl.level_cd, level_cd, gutl.geo_nm, geo_nm, gutl.efftv_start_dt, efftv_start_dt, gutl.efftv_end_dt, efftv_end_dt, gutl.org id prefix, org id prefix, gutl.franchise code, franchise code, gutl.sales org division code, sales org division code, gutl.sales org name, sales org name, gutl.upper_geo_id, upper_geo_id, gutl.provider id, provider id, gutl.last_updated_dt, last_updated_dt, gutl.upper_level_cd, upper_level_cd, fag.algn_struc_cd, fag_algn_struc_cd |
Main Table | Get Original Align Struct |
Main Table Alias | goas |
Joins | MDM LOV Parameter, lovp, Left |
Join Expressions | "goas"."fag_algn_struc_cd" = "lovp"."alignment structure code", goas_Left_lovp |
Output Columns | goas.algn_struc_cd, algn_struc_cd, goas.geo_id, geo_id, goas.team_id, team_id, goas.level_cd, level_cd, goas.geo_nm, geo_nm, goas.efftv_start_dt, efftv_start_dt, goas.efftv_end_dt, efftv_end_dt, goas.org id prefix, org id prefix, goas.franchise code, franchise code, goas.sales org division code, sales org division code, goas.upper_geo_id, upper_geo_id, goas.provider id, provider id, goas.last_updated_dt, last_updated_dt, goas.upper_level_cd, upper_level_cd, lovp.sales org name, sales org name, goas.fag_algn_struc_cd, fag_algn_struc_cd |
Main Table | flag(included recs) |
Main Table Alias | c1 |
Joins | Combine Rolling to 80, c2, Left |
Join Expressions | "c1"."algn_struc_cd" = "c2"."lower_algn_struc_cd" and "c1"."geo_id" = "c2"."lower_geo_id", c1_Left_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c2.efftv_start_dt, efftv_start_dt, c2.efftv_end_dt, efftv_end_dt, c1.geo_id, team_id, c2.lower_algn_struc_cd, lower_algn_struc_cd |
Main Table | Filter CG Team Geo Assoc |
Main Table Alias | c1 |
Joins | Exclude Rolling to 80 (included recs), c2, Inner |
Join Expressions | "c1"."algn_struc_cd" = "c2"."algn_struc_cd" and "c1"."geo_id" = "c2"."geo_id", c1_Inner_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.split_pct, split_pct, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.last_updated_dt, last_updated_dt |
Main Table | Get Valid Teams |
Main Table Alias | c1 |
Joins | Agg for Largest Team Record, c2, Inner |
Join Expressions | "c1"."algn_struc_cd" = "c2"."algn_struc_cd" and "c1"."split_pct" = "c2"."max_split_pct" and "c1"."team_id" = "c2"."team_id", c1_Inner_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.split_pct, split_pct, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.last_updated_dt, last_updated_dt |
Main Table | One Record Per Team |
Main Table Alias | c1 |
Joins | Combine Terr and Mgr, c2, Inner |
Join Expressions | "c1"."algn_struc_cd"="c2"."algn_struc_cd" and "c1"."max_geo_id"="c2"."geo_id", c1_Inner_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.max_geo_id, geo_id, c2.level_cd, level_cd, c2.geo_nm, geo_nm, c2.efftv_start_dt, efftv_start_dt, c2.efftv_end_dt, efftv_end_dt, c1.team_id, team_id |
Main Table | Combine Geos and Teams |
Main Table Alias | c1 |
Joins | outbound_src_mdm_lov_param, c2, Inner |
Join Expressions | "c1"."algn_struc_cd"="c2"."alignment structure code", c1_Inner_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.team_id, team_id, c2.org id prefix, org id prefix, c2.franchise code, franchise code, c2.sales org division code, sales org division code, c2.sales org name, sales org name |
Main Table | Get Organization ID (included recs) |
Main Table Alias | c1 |
Joins | included recs, c2, Left |
Join Expressions | "c1"."algn_struc_cd"="c2"."lower_algn_struc_cd" and "c1"."geo_id"="c2"."lower_geo_id" and "c1"."efftv_start_dt"<="c2"."efftv_end_dt" and "c1"."efftv_end_dt">="c2"."efftv_start_dt" , c1_Left_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.org id prefix, org id prefix, c1.franchise code, franchise code, c1.sales org division code, sales org division code, c1.sales org name, sales org name, c2.upper_geo_id, upper_geo_id, c2.efftv_start_dt, new_efftv_start_dt, c2.efftv_end_dt, new_efftv_end_dt |
Include Input Columns | Yes |
Calculations | case when "upper_geo_id"="algn_struc_cd" then "geo_id" else "upper_geo_id" end , upper_geo_id, 'OPCO_ETH', Provider ID, CURRENT_DATE, LAST_UPDATED_DT, case when "efftv_start_dt">"new_efftv_start_dt" then "efftv_start_dt" else "new_efftv_start_dt" end , efftv_start_dt, case when "efftv_end_dt">"new_efftv_end_dt" then "new_efftv_end_dt" else "efftv_end_dt" end , efftv_end_dt |
Main Table | Geo Id <4 digit |
Main Table Alias | c1 |
Joins | level CD is territory, c2, Left |
Join Expressions | "c1"."algn_struc_cd"="c2"."upper_algn_struc_cd" and "c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new" and "c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new" , c1_Left_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.geo_nm, geo_nm, c1.level_cd, level_cd, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c1.last_updated_dt_new, last_updated_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt |
Main Table | Param Geo Hier Start |
Main Table Alias | geo_hier |
Joins | outbound_dest_fiscal_calendar, fiscal, Left |
Join Expressions | "geo_hier"."geo_hier_efftv_end_dt"="fiscal"."efftv_end_dt", geo_hier_Left_fiscal |
Output Columns | geo_hier.upper_algn_struc_cd, upper_algn_struc_cd, geo_hier.lower_algn_struc_cd, lower_algn_struc_cd, geo_hier.geo_hier_efftv_start_dt, efftv_start_dt, geo_hier.geo_hier_efftv_end_dt, efftv_end_dt, geo_hier.level_cd, level_cd, geo_hier.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt, fiscal.fiscal_year_wk_end_dt, fiscal_year_wk_end_dt |
Main Table | Geo Hier: Filter Exclude Upper Struc Not Equal Lower |
Main Table Alias | c1 |
Joins | level CD is territory, c2, Left |
Join Expressions | "c1"."upper_algn_struc_cd"="c2"."upper_algn_struc_cd" and "c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new" and "c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new" , c1_Left_c2 |
Output Columns | c1.upper_geo_id, upper_geo_id, c1.lower_geo_id, lower_geo_id, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt |
Include Input Columns | Yes |
Calculations | case when "efftv_start_dt">"new_efftv_start_dt" then "efftv_start_dt" else "new_efftv_start_dt" end, EFFTV_START_DT_2, case when "efftv_end_dt">"new_efftv_end_dt" then "new_efftv_end_dt" else "efftv_end_dt" end, EFFTV_END_DT_2, case when len("lower_geo_id")=2 then "lower_algn_struc_cd" else "upper_geo_id" end, UPPER_GEO_ID_2 |
Main Table | Geo Hier Merge Terr and Upper (included recs) |
Main Table Alias | c1 |
Joins | Get Divisions Rolling to 80, c2, Inner |
Join Expressions | "c1"."upper_algn_struc_cd"="c2"."lower_algn_struc_cd" and "c1"."upper_geo_id"="c2"."lower_geo_id", c1_Inner_c2 |
Output Columns | c1.lower_algn_struc_cd, lower_algn_struc_cd, c1.lower_geo_id, lower_geo_id, c1.upper_algn_struc_cd, upper_algn_struc_cd, c1.upper_geo_id, upper_geo_id, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt |
Main Table | Geo_Id =2 digit |
Main Table Alias | c1 |
Joins | level CD is division, c2, Left |
Join Expressions | "c1"."upper_algn_struc_cd"="c2"."upper_algn_struc_cd" and "c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new" and "c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new", c1_Left_c2 |
Output Columns | c1.upper_geo_id, upper_geo_id, c1.lower_geo_id, lower_geo_id, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt |
Main Table | Geo_Id =3 digit |
Main Table Alias | c1 |
Joins | level CD is division, c2, Left |
Join Expressions | "c1"."upper_algn_struc_cd"="c2"."upper_algn_struc_cd" and "c1"."lower_algn_struc_cd"="c2"."lower_algn_struc_cd" and "c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new" and "c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new", c1_Left_c2 |
Output Columns | c1.upper_geo_id, upper_geo_id, c1.lower_geo_id, lower_geo_id, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt |
Main Table | outbound_src_geo_management |
Main Table Alias | filter |
Joins | outbound_dest_fiscal_calendar, fiscal, Left |
Join Expressions | "filter"."efftv_start_dt"="fiscal"."efftv_start_dt", filter_Left_fiscal |
Output Columns | filter.algn_struc_cd, algn_struc_cd, filter.geo_id, geo_id, filter.geo_nm, geo_nm, filter.level_cd, level_cd, filter.efftv_start_dt, filter_efftv_start_dt, filter.efftv_end_dt, filter_efftv_end_dt, filter.last_updated_dt, last_updated, fiscal.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt |
Main Table | outbound_src_param_geo_hierarchy |
Main Table Alias | geo_hier |
Joins | outbound_dest_fiscal_calendar, fiscal, Left |
Join Expressions | "geo_hier"."efftv_start_dt"="fiscal"."efftv_start_dt", geo_hier_Left_fiscal |
Output Columns | geo_hier.upper_algn_struc_cd, upper_algn_struc_cd, geo_hier.lower_algn_struc_cd, lower_algn_struc_cd, geo_hier.efftv_start_dt, geo_hier_efftv_start_dt, geo_hier.efftv_end_dt, geo_hier_efftv_end_dt, geo_hier.level_cd, level_cd, fiscal.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt |
Main Table | Geo Hier: Rename |
Main Table Alias | filter |
Joins | outbound_dest_fiscal_calendar, fiscal, Left |
Join Expressions | "filter"."efftv_start_dt"="fiscal"."efftv_start_dt", filter_Left_fiscal |
Output Columns | filter.lower_algn_struc_cd, lower_algn_struc_cd, filter.lower_geo_id, lower_geo_id, filter.upper_algn_struc_cd, upper_algn_struc_cd, filter.upper_geo_id, upper_geo_id, filter.efftv_start_dt, filter_efftv_start_dt, filter.efftv_end_dt, filter_efftv_end_dt, filter.last_updated, last_updated, fiscal.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt |
Main Table | Geo Hier Start Date |
Main Table Alias | start |
Joins | outbound_dest_fiscal_calendar, fiscal, Left |
Join Expressions | "start"."filter_efftv_end_dt"="fiscal"."efftv_end_dt", start_Left_fiscal |
Output Columns | start.lower_algn_struc_cd, lower_algn_struc_cd, start.lower_geo_id, lower_geo_id, start.upper_algn_struc_cd, upper_algn_struc_cd, start.upper_geo_id, upper_geo_id, start.filter_efftv_start_dt, filter_efftv_start_dt, start.filter_efftv_end_dt, filter_efftv_end_dt, start.last_updated, last_updated, start.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt, fiscal.fiscal_year_wk_end_dt, fiscal_year_wk_end_dt |
Include Input Columns | Yes |
Calculations | CASE WHEN "fiscal_year_wk_start_dt" is NULL THEN '1900-01-01' ELSE "fiscal_year_wk_start_dt" END, EFFTV_START_DT_new, CASE WHEN "fiscal_year_wk_end_dt" is NULL THEN '9999-12-31' ELSE "fiscal_year_wk_end_dt" END, EFFTV_END_DT_new, DATE_PART_YEAR(to_date("last_updated",'YYYY-mm-dd')) || lpad(extract(month from(to_date("last_updated",'YYYY-mm-dd'))),2,'00') || lpad(extract(day from(to_date("last_updated",'YYYY-mm-dd'))),2,'00'), LAST_UPDATED_DT_new, len("lower_geo_id"), len_geo_id, "upper_algn_struc_cd"!="lower_algn_struc_cd", compare struc code |
Script | import datetime x = datetime.datetime.now() x=str(x).split('.') x=x[0] print('time_stamp :'+str(x)) print('Archive_temp_current_table :'+str(Archive_temp_current_table)) print('Archive_temp_current_file :'+str(Archive_temp_current_file)) Archive_temp_current_file=Archive_temp_current_file+'_'+x+'__' print('Archive_temp_current_file upt :'+str(Archive_temp_current_file)) context.updateVariable('Archive_temp_current_file', str(Archive_temp_current_file)) print('Archive_type :'+Archive_type) #dynamically change path for Archive if Archive_type=='ONEMD': S3_temp_Archive_Location=S3_ONEMD_Archive_Location print('Archive Locations :'+str(S3_temp_Archive_Location)) elif Archive_type=='MDM21': S3_temp_Archive_Location=S3_MDM21_Archive_Location print('Archive Locations :'+str(S3_temp_Archive_Location)) else: S3_temp_Archive_Location=S3_EUSS_Archive_Location print('Archive Locations :'+str(S3_temp_Archive_Location)) |
Interpreter | Jython |
Schema | ${Schema_Default} |
Table Name | ${Archive_temp_current_table} |
S3 URL Location | ${S3_ONEMD_Archive_Location} |
S3 Object Prefix | ${Archive_temp_current_file} |
IAM Role Arn | arn:aws:iam::775229046089:role/RedshiftS3Athna |
Generate Manifest | No |
Data File Type | Delimited |
Delimiter | | |
Compress Data | Yes |
Compression Type | GZIP |
Null As | |
Escape | No |
Allow Overwrites | Yes |
Parallel | No |
Add Quotes | No |
Max File Size (MB) | |
Include Header | No |
Encryption | None |
Schema | ${Schema_Default} |
Table Name | ${Archive_temp_current_table} |
S3 URL Location | ${S3_MDM21_Archive_Location} |
S3 Object Prefix | ${Archive_temp_current_file} |
IAM Role Arn | arn:aws:iam::775229046089:role/RedshiftS3Athna |
Generate Manifest | No |
Data File Type | Delimited |
Delimiter | | |
Compress Data | Yes |
Compression Type | GZIP |
Null As | |
Escape | No |
Allow Overwrites | Yes |
Parallel | No |
Add Quotes | No |
Max File Size (MB) | |
Include Header | No |
Encryption | None |
Schema | ${Schema_Default} |
Table Name | ${Archive_temp_current_table} |
S3 URL Location | ${S3_EUSS_Archive_Location} |
S3 Object Prefix | ${Archive_temp_current_file} |
IAM Role Arn | arn:aws:iam::775229046089:role/RedshiftS3Athna |
Generate Manifest | No |
Data File Type | Delimited |
Delimiter | | |
Compress Data | Yes |
Compression Type | GZIP |
Null As | |
Escape | No |
Allow Overwrites | Yes |
Parallel | No |
Add Quotes | No |
Max File Size (MB) | |
Include Header | No |
Encryption | None |